import os
import zipfile
import io
from flask import current_app, send_file
from models.models import User, Sentence, Recording, UserSentenceAssignment, AssignmentTemplate, db
from sqlalchemy import or_, func, and_, case
import random
from datetime import datetime

class AdminController:
    @staticmethod
    def admin_login(data):
        username = data.get('username')
        password = data.get('password')
        
        # Find user by username
        user = User.query.filter_by(username=username).first()
        
        # Check if user exists, password is correct, and user is admin
        if not user or not user.check_password(password) or user.role != 'admin':
            return {'code': 401, 'message': '用户名或密码错误，或无管理员权限', 'data': None}
        
        # Import here to avoid circular import
        from flask_jwt_extended import create_access_token
        
        # Generate token
        token = create_access_token(identity=str(user.id), additional_claims={"role": user.role})
        
        return {
            'code': 200,
            'message': '登录成功',
            'data': {
                'token': token,
                'user': user.to_dict()
            }
        }
    
    @staticmethod
    def get_users(page=1, limit=10, search=None):
        query = User.query
        
        # Apply search filter if provided
        if search:
            query = query.filter(or_(
                User.username.like(f'%{search}%'),
                User.ethnicity.like(f'%{search}%'),
                User.mandarin_level.like(f'%{search}%')
            ))
        
        # Get total count
        total = query.count()
        
        # Apply pagination
        users = query.paginate(page=page, per_page=limit, error_out=False).items
        
        return {
            'code': 200,
            'message': '获取成功',
            'data': {
                'total': total,
                'users': [user.to_dict() for user in users]
            }
        }
    
    @staticmethod
    def add_user(data):
        username = data.get('username')
        password = data.get('password')
        
        # Check if username already exists
        if User.query.filter_by(username=username).first():
            return {'code': 400, 'message': '用户名已存在', 'data': None}
        
        # Create new user
        user = User(
            username=username,
            age=data.get('age'),
            gender=data.get('gender'),
            ethnicity=data.get('ethnicity'),
            mandarin_level=data.get('mandarin_level'),
            role=data.get('role', 'user')
        )
        user.set_password(password)
        
        # Save to database
        db.session.add(user)
        db.session.commit()
        
        return {
            'code': 200,
            'message': '添加成功',
            'data': user.to_dict()
        }
    
    @staticmethod
    def update_user(user_id, data):
        user = User.query.get(user_id)
        
        if not user:
            return {'code': 404, 'message': '用户不存在', 'data': None}
        
        # Update fields if provided
        if 'username' in data and data['username'] != user.username:
            # Check if new username is already taken
            if User.query.filter_by(username=data['username']).first():
                return {'code': 400, 'message': '用户名已存在', 'data': None}
            user.username = data['username']
            
        if 'age' in data:
            user.age = data['age']
            
        if 'gender' in data:
            user.gender = data['gender']
            
        if 'ethnicity' in data:
            user.ethnicity = data['ethnicity']
            
        if 'mandarin_level' in data:
            user.mandarin_level = data['mandarin_level']
            
        if 'role' in data:
            user.role = data['role']
        
        # Save changes
        db.session.commit()
        
        return {
            'code': 200,
            'message': '更新成功',
            'data': user.to_dict()
        }
    
    @staticmethod
    def delete_user(user_id):
        user = User.query.get(user_id)
        
        if not user:
            return {'code': 404, 'message': '用户不存在', 'data': None}
        
        # Delete user's recordings first
        recordings = Recording.query.filter_by(user_id=user_id).all()
        for recording in recordings:
            # Delete recording file
            try:
                filename = os.path.basename(recording.audio_file)
                file_path = os.path.join(current_app.config['UPLOAD_FOLDER'], filename)
                if os.path.exists(file_path):
                    os.remove(file_path)
            except Exception as e:
                # Log error but continue
                print(f"Error removing recording file: {e}")
        
        # Delete recordings from database
        Recording.query.filter_by(user_id=user_id).delete()
        
        # Delete user
        db.session.delete(user)
        db.session.commit()
        
        return {
            'code': 200,
            'message': '删除成功',
            'data': None
        }
    
    @staticmethod
    def get_sentences(page=1, limit=10, search=None):
        query = Sentence.query
        
        # Apply search filter if provided
        if search:
            query = query.filter(Sentence.content.like(f'%{search}%'))
        
        # Get total count
        total = query.count()
        
        # Apply pagination
        sentences = query.paginate(page=page, per_page=limit, error_out=False).items
        
        return {
            'code': 200,
            'message': '获取成功',
            'data': {
                'total': total,
                'sentences': [sentence.to_dict() for sentence in sentences]
            }
        }
    
    @staticmethod
    def add_sentence(data):
        content = data.get('content')
        
        if not content:
            return {'code': 400, 'message': '内容不能为空', 'data': None}
        
        # Create new sentence
        sentence = Sentence(content=content)
        db.session.add(sentence)
        db.session.commit()
        
        return {
            'code': 200,
            'message': '添加成功',
            'data': sentence.to_dict()
        }
    
    @staticmethod
    def batch_import_sentences(file):
        if not file:
            return {'code': 400, 'message': '文件不能为空', 'data': None}
        
        # Read file content
        content = file.read().decode('utf-8')
        lines = content.strip().split('\n')
        
        # Filter out empty lines
        sentences_to_add = [line.strip() for line in lines if line.strip()]
        
        # Add sentences to database
        imported_count = 0
        for sentence_content in sentences_to_add:
            # Check if sentence already exists
            if not Sentence.query.filter_by(content=sentence_content).first():
                sentence = Sentence(content=sentence_content)
                db.session.add(sentence)
                imported_count += 1
        
        db.session.commit()
        
        # Get total count after import
        total_count = Sentence.query.count()
        
        return {
            'code': 200,
            'message': '导入成功',
            'data': {
                'imported_count': imported_count,
                'total_count': total_count
            }
        }
    
    @staticmethod
    def update_sentence(sentence_id, data):
        sentence = Sentence.query.get(sentence_id)
        
        if not sentence:
            return {'code': 404, 'message': '句子不存在', 'data': None}
        
        content = data.get('content')
        if not content:
            return {'code': 400, 'message': '内容不能为空', 'data': None}
        
        sentence.content = content
        db.session.commit()
        
        return {
            'code': 200,
            'message': '更新成功',
            'data': sentence.to_dict()
        }
    
    @staticmethod
    def delete_sentence(sentence_id):
        sentence = Sentence.query.get(sentence_id)
        
        if not sentence:
            return {'code': 404, 'message': '句子不存在', 'data': None}
        
        # Delete related recordings first
        recordings = Recording.query.filter_by(sentence_id=sentence_id).all()
        for recording in recordings:
            # Delete recording file
            try:
                filename = os.path.basename(recording.audio_file)
                file_path = os.path.join(current_app.config['UPLOAD_FOLDER'], filename)
                if os.path.exists(file_path):
                    os.remove(file_path)
            except Exception as e:
                # Log error but continue
                print(f"Error removing recording file: {e}")
        
        # Delete recordings from database
        Recording.query.filter_by(sentence_id=sentence_id).delete()
        
        # Delete sentence
        db.session.delete(sentence)
        db.session.commit()
        
        return {
            'code': 200,
            'message': '删除成功',
            'data': None
        }
    
    @staticmethod
    def get_recordings(page=1, limit=10, username=None, sentence_content=None):
        # Start with a join to get username and sentence content
        query = db.session.query(Recording, User.username, Sentence.content).join(
            User, Recording.user_id == User.id
        ).join(
            Sentence, Recording.sentence_id == Sentence.id
        )
        
        # Apply filters if provided
        if username:
            query = query.filter(User.username.like(f'%{username}%'))
        
        if sentence_content:
            query = query.filter(Sentence.content.like(f'%{sentence_content}%'))
        
        # Get total count
        total = query.count()
        
        # Apply pagination
        results = query.paginate(page=page, per_page=limit, error_out=False).items
        
        # Format results
        recordings_data = []
        for recording, username, content in results:
            recording_dict = recording.to_dict()
            recording_dict['username'] = username
            recording_dict['sentence_content'] = content
            recordings_data.append(recording_dict)
        
        return {
            'code': 200,
            'message': '获取成功',
            'data': {
                'total': total,
                'recordings': recordings_data
            }
        }
    
    @staticmethod
    def get_recording(recording_id):
        # Join to get username and sentence content
        result = db.session.query(Recording, User.username, Sentence.content).join(
            User, Recording.user_id == User.id
        ).join(
            Sentence, Recording.sentence_id == Sentence.id
        ).filter(Recording.id == recording_id).first()
        
        if not result:
            return {'code': 404, 'message': '录音不存在', 'data': None}
        
        recording, username, content = result
        recording_dict = recording.to_dict()
        recording_dict['username'] = username
        recording_dict['sentence_content'] = content
        
        return {
            'code': 200,
            'message': '获取成功',
            'data': recording_dict
        }
    
    @staticmethod
    def delete_recording(recording_id):
        recording = Recording.query.get(recording_id)
        
        if not recording:
            return {'code': 404, 'message': '录音不存在', 'data': None}
        
        # Delete recording file
        try:
            filename = os.path.basename(recording.audio_file)
            file_path = os.path.join(current_app.config['UPLOAD_FOLDER'], filename)
            if os.path.exists(file_path):
                os.remove(file_path)
        except Exception as e:
            # Log error but continue
            print(f"Error removing recording file: {e}")
        
        # Delete recording from database
        db.session.delete(recording)
        db.session.commit()
        
        return {
            'code': 200,
            'message': '删除成功',
            'data': None
        }
    
    @staticmethod
    def download_recording(recording_id):
        recording = Recording.query.get(recording_id)
        
        if not recording:
            return None
        
        # Extract filename from the audio_file path
        filename = os.path.basename(recording.audio_file)
        file_path = os.path.join(current_app.config['UPLOAD_FOLDER'], filename)
        
        if not os.path.exists(file_path):
            return None
            
        return file_path
    
    @staticmethod
    def export_user_recordings(user_id):
        user = User.query.get(user_id)
        
        if not user:
            return None
        
        recordings = Recording.query.filter_by(user_id=user_id).all()
        
        if not recordings:
            return None
        
        # Create a memory file for the zip
        memory_file = io.BytesIO()
        
        # Create zip file
        with zipfile.ZipFile(memory_file, 'w', zipfile.ZIP_DEFLATED) as zf:
            for recording in recordings:
                filename = os.path.basename(recording.audio_file)
                file_path = os.path.join(current_app.config['UPLOAD_FOLDER'], filename)
                
                if os.path.exists(file_path):
                    # Get the sentence content
                    sentence = Sentence.query.get(recording.sentence_id)
                    if sentence:
                        # Use sentence content in filename for better identification
                        # Replace invalid filename characters
                        safe_content = sentence.content[:30].replace('/', '_').replace('\\', '_')
                        zip_filename = f"{user.username}_{safe_content}.wav"
                    else:
                        zip_filename = filename
                    
                    zf.write(file_path, zip_filename)
        
        # Reset file pointer
        memory_file.seek(0)
        
        return memory_file
    
    @staticmethod
    def export_sentence_recordings(sentence_id):
        sentence = Sentence.query.get(sentence_id)
        
        if not sentence:
            return None
        
        recordings = Recording.query.filter_by(sentence_id=sentence_id).all()
        
        if not recordings:
            return None
        
        # Create a memory file for the zip
        memory_file = io.BytesIO()
        
        # Create zip file
        with zipfile.ZipFile(memory_file, 'w', zipfile.ZIP_DEFLATED) as zf:
            for recording in recordings:
                filename = os.path.basename(recording.audio_file)
                file_path = os.path.join(current_app.config['UPLOAD_FOLDER'], filename)
                
                if os.path.exists(file_path):
                    # Get the user
                    user = User.query.get(recording.user_id)
                    if user:
                        # Use username in filename for better identification
                        zip_filename = f"{user.username}_{filename}"
                    else:
                        zip_filename = filename
                    
                    zf.write(file_path, zip_filename)
        
        # Reset file pointer
        memory_file.seek(0)
        
        return memory_file
    
    @staticmethod
    def export_all_recordings():
        recordings = Recording.query.all()
        
        if not recordings:
            return None
        
        # Create a memory file for the zip
        memory_file = io.BytesIO()
        
        # Create zip file
        with zipfile.ZipFile(memory_file, 'w', zipfile.ZIP_DEFLATED) as zf:
            for recording in recordings:
                filename = os.path.basename(recording.audio_file)
                file_path = os.path.join(current_app.config['UPLOAD_FOLDER'], filename)
                
                if os.path.exists(file_path):
                    # Get the user and sentence
                    user = User.query.get(recording.user_id)
                    sentence = Sentence.query.get(recording.sentence_id)
                    
                    if user and sentence:
                        # Use username and sentence content in filename for better identification
                        safe_content = sentence.content[:20].replace('/', '_').replace('\\', '_')
                        zip_filename = f"{user.username}_{safe_content}.wav"
                    else:
                        zip_filename = filename
                    
                    zf.write(file_path, zip_filename)
        
        # Reset file pointer
        memory_file.seek(0)
        
        return memory_file
    
    @staticmethod
    def get_stats():
        user_count = User.query.filter_by(role='user').count()
        admin_count = User.query.filter_by(role='admin').count()
        sentence_count = Sentence.query.count()
        recording_count = Recording.query.count()
        
        return {
            'code': 200,
            'message': '获取成功',
            'data': {
                'userCount': user_count,
                'adminCount': admin_count,
                'sentenceCount': sentence_count,
                'recordingCount': recording_count
            }
        }
    
    @staticmethod
    def get_latest_recordings(limit=5):
        # Join to get username and sentence content
        results = db.session.query(Recording, User.username, Sentence.content).join(
            User, Recording.user_id == User.id
        ).join(
            Sentence, Recording.sentence_id == Sentence.id
        ).order_by(Recording.id.desc()).limit(limit).all()
        
        # Format results
        recordings_data = []
        for recording, username, content in results:
            recording_dict = recording.to_dict()
            recording_dict['username'] = username
            recording_dict['sentence_content'] = content
            recordings_data.append(recording_dict)
        
        return {
            'code': 200,
            'message': '获取成功',
            'data': recordings_data
        }
    
    @staticmethod
    def get_user_recordings_count(user_id):
        """获取指定用户的录音数量统计"""
        user = User.query.get(user_id)
        
        if not user:
            return {'code': 404, 'message': '用户不存在', 'data': None}
        
        # 获取该用户的录音数量
        count = Recording.query.filter_by(user_id=user_id).count()
        
        # 获取系统中的句子总数
        total_sentences = Sentence.query.count()
        
        # 计算完成率
        completion_rate = 0
        if total_sentences > 0:
            completion_rate = round((count / total_sentences) * 100, 2)
        
        return {
            'code': 200,
            'message': '获取成功',
            'data': {
                'user_id': user_id,
                'username': user.username,
                'recording_count': count,
                'total_sentences': total_sentences,
                'completion_rate': completion_rate
            }
        }
    
    @staticmethod
    def get_sentence_recordings_count(sentence_id):
        """获取指定句子的录音数量统计"""
        sentence = Sentence.query.get(sentence_id)
        
        if not sentence:
            return {'code': 404, 'message': '句子不存在', 'data': None}
        
        # 获取该句子的录音数量
        count = Recording.query.filter_by(sentence_id=sentence_id).count()
        
        # 获取系统中的用户总数（仅普通用户）
        total_users = User.query.filter_by(role='user').count()
        
        # 计算完成率
        completion_rate = 0
        if total_users > 0:
            completion_rate = round((count / total_users) * 100, 2)
        
        return {
            'code': 200,
            'message': '获取成功',
            'data': {
                'sentence_id': sentence_id,
                'content': sentence.content,
                'recording_count': count,
                'total_users': total_users,
                'completion_rate': completion_rate
            }
        }

    # ==================== 句子分配管理功能 ====================

    @staticmethod
    def assign_sentences_to_user(user_id, sentence_ids, replace_existing=True):
        """给用户分配句子"""
        try:
            # 验证用户是否存在
            user = User.query.get(user_id)
            if not user:
                return {'code': 404, 'message': '用户不存在', 'data': None}

            # 验证句子是否存在
            sentences = Sentence.query.filter(Sentence.id.in_(sentence_ids)).all()
            if len(sentences) != len(sentence_ids):
                return {'code': 400, 'message': '部分句子不存在', 'data': None}

            # 如果需要替换现有分配，先删除
            if replace_existing:
                UserSentenceAssignment.query.filter_by(user_id=user_id).delete()

            # 创建新的分配记录
            assignments = []
            for i, sentence_id in enumerate(sentence_ids, 1):
                # 检查是否已存在分配（避免重复）
                existing = UserSentenceAssignment.query.filter_by(
                    user_id=user_id,
                    sentence_id=sentence_id
                ).first()

                if not existing:
                    assignment = UserSentenceAssignment(
                        user_id=user_id,
                        sentence_id=sentence_id,
                        sequence_order=i,
                        status='pending'
                    )
                    assignments.append(assignment)
                    db.session.add(assignment)

            db.session.commit()

            return {
                'code': 200,
                'message': '分配成功',
                'data': {
                    'user_id': user_id,
                    'assigned_count': len(assignments),
                    'total_sentences': len(sentence_ids),
                    'assignments': [a.to_dict() for a in assignments]
                }
            }

        except Exception as e:
            db.session.rollback()
            return {'code': 500, 'message': f'分配失败: {str(e)}', 'data': None}

    @staticmethod
    def get_user_assignments(user_id):
        """获取用户的句子分配情况"""
        try:
            # 验证用户是否存在
            user = User.query.get(user_id)
            if not user:
                return {'code': 404, 'message': '用户不存在', 'data': None}

            # 查询用户的分配情况
            assignments = db.session.query(
                UserSentenceAssignment,
                Sentence.content
            ).join(
                Sentence, UserSentenceAssignment.sentence_id == Sentence.id
            ).filter(
                UserSentenceAssignment.user_id == user_id
            ).order_by(
                UserSentenceAssignment.sequence_order
            ).all()

            # 构建分配数据
            assignment_data = []
            for assignment, content in assignments:
                # 检查是否已录制
                recording = Recording.query.filter_by(
                    user_id=user_id,
                    sentence_id=assignment.sentence_id
                ).first()

                # 如果有录音但分配状态还是pending，更新状态
                if recording and assignment.status == 'pending':
                    assignment.status = 'completed'
                    assignment.completed_at = recording.created_at
                    db.session.commit()

                assignment_data.append({
                    'id': assignment.id,
                    'sentence_id': assignment.sentence_id,
                    'content': content,
                    'sequence_order': assignment.sequence_order,
                    'status': assignment.status,
                    'assigned_at': assignment.assigned_at.isoformat() if assignment.assigned_at else None,
                    'completed_at': assignment.completed_at.isoformat() if assignment.completed_at else None,
                    'has_recording': recording is not None
                })

            # 计算进度统计
            total = len(assignment_data)
            completed = sum(1 for item in assignment_data if item['status'] == 'completed')
            pending = total - completed
            completion_rate = round(completed / total * 100, 1) if total > 0 else 0

            return {
                'code': 200,
                'message': '获取成功',
                'data': {
                    'user_id': user_id,
                    'user_info': user.to_dict(),
                    'assignments': assignment_data,
                    'progress': {
                        'total': total,
                        'completed': completed,
                        'pending': pending,
                        'completion_rate': completion_rate
                    }
                }
            }

        except Exception as e:
            return {'code': 500, 'message': f'获取失败: {str(e)}', 'data': None}

    @staticmethod
    def assign_random_sentences(user_id, count=5, exclude_completed=True):
        """随机分配句子给用户"""
        try:
            # 验证用户是否存在
            user = User.query.get(user_id)
            if not user:
                return {'code': 404, 'message': '用户不存在', 'data': None}

            # 获取可分配的句子
            query = Sentence.query

            if exclude_completed:
                # 排除用户已录制的句子
                recorded_sentence_ids = db.session.query(Recording.sentence_id).filter_by(user_id=user_id).subquery()
                query = query.filter(~Sentence.id.in_(recorded_sentence_ids))

                # 排除已分配但未录制的句子
                assigned_sentence_ids = db.session.query(UserSentenceAssignment.sentence_id).filter_by(user_id=user_id).subquery()
                query = query.filter(~Sentence.id.in_(assigned_sentence_ids))

            available_sentences = query.all()

            if len(available_sentences) < count:
                return {
                    'code': 400,
                    'message': f'可分配的句子不足，只有{len(available_sentences)}个可用句子',
                    'data': None
                }

            # 随机选择句子
            selected_sentences = random.sample(available_sentences, count)
            sentence_ids = [s.id for s in selected_sentences]

            # 调用分配方法
            return AdminController.assign_sentences_to_user(user_id, sentence_ids, replace_existing=False)

        except Exception as e:
            return {'code': 500, 'message': f'随机分配失败: {str(e)}', 'data': None}

    @staticmethod
    def batch_assign_sentences(user_ids, sentence_ids=None, assignment_type='fixed', count=5):
        """批量分配句子给多个用户"""
        try:
            results = []

            for user_id in user_ids:
                if assignment_type == 'random':
                    result = AdminController.assign_random_sentences(user_id, count)
                else:  # fixed
                    if not sentence_ids:
                        return {'code': 400, 'message': '固定分配模式需要提供句子ID列表', 'data': None}
                    result = AdminController.assign_sentences_to_user(user_id, sentence_ids)

                results.append({
                    'user_id': user_id,
                    'success': result['code'] == 200,
                    'message': result['message'],
                    'assigned_count': result['data']['assigned_count'] if result['code'] == 200 else 0
                })

            success_count = sum(1 for r in results if r['success'])

            return {
                'code': 200,
                'message': f'批量分配完成，成功{success_count}个，失败{len(user_ids) - success_count}个',
                'data': {
                    'total_users': len(user_ids),
                    'success_count': success_count,
                    'failed_count': len(user_ids) - success_count,
                    'results': results
                }
            }

        except Exception as e:
            return {'code': 500, 'message': f'批量分配失败: {str(e)}', 'data': None}

    @staticmethod
    def get_assignment_overview():
        """获取所有用户的分配概览"""
        try:
            # 查询所有有分配的用户
            users_with_assignments = db.session.query(
                User.id,
                User.username,
                func.count(UserSentenceAssignment.id).label('total_assigned'),
                func.sum(case((UserSentenceAssignment.status == 'completed', 1), else_=0)).label('completed'),
                func.max(UserSentenceAssignment.assigned_at).label('last_activity')
            ).join(
                UserSentenceAssignment, User.id == UserSentenceAssignment.user_id
            ).group_by(
                User.id, User.username
            ).all()

            # 构建用户数据
            users_data = []
            for user_id, username, total_assigned, completed, last_activity in users_with_assignments:
                completed = int(completed or 0)
                total_assigned = int(total_assigned)
                pending = total_assigned - completed
                completion_rate = round(completed / total_assigned * 100, 1) if total_assigned > 0 else 0

                users_data.append({
                    'user_id': user_id,
                    'username': username,
                    'total_assigned': total_assigned,
                    'completed': completed,
                    'pending': pending,
                    'completion_rate': completion_rate,
                    'last_activity': last_activity.isoformat() if last_activity else None
                })

            # 计算总体统计
            total_users = User.query.filter_by(role='user').count()
            users_with_assignments_count = len(users_data)
            total_assignments = sum(int(user['total_assigned']) for user in users_data)
            total_completed = sum(int(user['completed']) for user in users_data)
            average_completion_rate = round(sum(float(user['completion_rate']) for user in users_data) / len(users_data), 1) if users_data else 0

            return {
                'code': 200,
                'message': '获取成功',
                'data': {
                    'users': users_data,
                    'statistics': {
                        'total_users': total_users,
                        'users_with_assignments': users_with_assignments_count,
                        'users_without_assignments': total_users - users_with_assignments_count,
                        'total_assignments': total_assignments,
                        'total_completed': total_completed,
                        'average_completion_rate': average_completion_rate
                    }
                }
            }

        except Exception as e:
            return {'code': 500, 'message': f'获取概览失败: {str(e)}', 'data': None}

    @staticmethod
    def get_sentence_usage_stats():
        """获取句子使用统计"""
        try:
            # 查询每个句子的分配和完成情况
            sentence_stats = db.session.query(
                Sentence.id,
                Sentence.content,
                func.count(UserSentenceAssignment.id).label('assigned_count'),
                func.sum(case((UserSentenceAssignment.status == 'completed', 1), else_=0)).label('completed_count')
            ).outerjoin(
                UserSentenceAssignment, Sentence.id == UserSentenceAssignment.sentence_id
            ).group_by(
                Sentence.id, Sentence.content
            ).all()

            # 构建统计数据
            stats_data = []
            for sentence_id, content, assigned_count, completed_count in sentence_stats:
                assigned_count = int(assigned_count or 0)
                completed_count = int(completed_count or 0)
                completion_rate = round(completed_count / assigned_count * 100, 1) if assigned_count > 0 else 0

                stats_data.append({
                    'sentence_id': sentence_id,
                    'content': content,
                    'assigned_count': assigned_count,
                    'completed_count': completed_count,
                    'pending_count': assigned_count - completed_count,
                    'completion_rate': completion_rate
                })

            # 按分配次数排序
            stats_data.sort(key=lambda x: x['assigned_count'], reverse=True)

            return {
                'code': 200,
                'message': '获取成功',
                'data': stats_data
            }

        except Exception as e:
            return {'code': 500, 'message': f'获取句子统计失败: {str(e)}', 'data': None}

    # ==================== 分配模板管理 ====================

    @staticmethod
    def create_assignment_template(name, description, sentence_ids, category=None):
        """创建分配模板"""
        try:
            # 验证句子是否存在
            sentences = Sentence.query.filter(Sentence.id.in_(sentence_ids)).all()
            if len(sentences) != len(sentence_ids):
                return {'code': 400, 'message': '部分句子不存在', 'data': None}

            # 创建模板
            template = AssignmentTemplate(
                name=name,
                description=description,
                sentence_ids=sentence_ids,
                category=category
            )

            db.session.add(template)
            db.session.commit()

            return {
                'code': 200,
                'message': '模板创建成功',
                'data': template.to_dict()
            }

        except Exception as e:
            db.session.rollback()
            return {'code': 500, 'message': f'创建模板失败: {str(e)}', 'data': None}

    @staticmethod
    def get_assignment_templates():
        """获取所有分配模板"""
        try:
            templates = AssignmentTemplate.query.order_by(AssignmentTemplate.created_at.desc()).all()

            return {
                'code': 200,
                'message': '获取成功',
                'data': [template.to_dict() for template in templates]
            }

        except Exception as e:
            return {'code': 500, 'message': f'获取模板失败: {str(e)}', 'data': None}

    @staticmethod
    def apply_template_to_users(template_id, user_ids):
        """将模板应用到用户"""
        try:
            # 获取模板
            template = AssignmentTemplate.query.get(template_id)
            if not template:
                return {'code': 404, 'message': '模板不存在', 'data': None}

            # 批量分配
            result = AdminController.batch_assign_sentences(
                user_ids=user_ids,
                sentence_ids=template.sentence_ids,
                assignment_type='fixed'
            )

            if result['code'] == 200:
                result['message'] = f'模板"{template.name}"应用成功'
                result['data']['template_name'] = template.name

            return result

        except Exception as e:
            return {'code': 500, 'message': f'应用模板失败: {str(e)}', 'data': None}

    @staticmethod
    def delete_assignment_template(template_id):
        """删除分配模板"""
        try:
            template = AssignmentTemplate.query.get(template_id)
            if not template:
                return {'code': 404, 'message': '模板不存在', 'data': None}

            db.session.delete(template)
            db.session.commit()

            return {
                'code': 200,
                'message': '模板删除成功',
                'data': None
            }

        except Exception as e:
            db.session.rollback()
            return {'code': 500, 'message': f'删除模板失败: {str(e)}', 'data': None}

    @staticmethod
    def clear_user_assignments(user_id):
        """清空用户的所有分配"""
        try:
            user = User.query.get(user_id)
            if not user:
                return {'code': 404, 'message': '用户不存在', 'data': None}

            deleted_count = UserSentenceAssignment.query.filter_by(user_id=user_id).delete()
            db.session.commit()

            return {
                'code': 200,
                'message': f'已清空用户分配，删除了{deleted_count}条记录',
                'data': {'deleted_count': deleted_count}
            }

        except Exception as e:
            db.session.rollback()
            return {'code': 500, 'message': f'清空分配失败: {str(e)}', 'data': None}